package com.primeton.huang;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

public class CreateTable {

    private static int num = 0;

    public static void main(String[] args) throws Exception {

    }


    /**
     * Mysql到Mysql自动建表
     *
     *
     * @param sourceUrl 源库url，例：jdbc:mysql://localhost:3306
     * @param sourceDataBase 源库名，例：mdm
     * @param sourceUser 源库用户名，例：user
     * @param sourcePassword 源库密码，例：1234
     * @param tableName 表名，不填则默认全部，例:t_a,t_b
     * @param targetUrl 目标库url，例：jdbc:mysql://localhost:3306
     * @param targetDataBase 目标库名，例：md
     * @param targetUser 目标库用户名，例：user
     * @param targetPassword 目标库密码，例：1234
     */
    public static void autoCreateTableMysqlToMysql(String sourceUrl,String sourceDataBase,String sourceUser,String sourcePassword,String tableName,
                                                   String targetUrl,String targetDataBase,String targetUser,String targetPassword) {

        try {
            List<String> createTableList = getCreateTableSqlMysql(sourceUrl, sourceDataBase, sourceUser, sourcePassword,tableName);
            executeCreateTableMysql(targetUrl, targetDataBase, targetUser, targetPassword,createTableList);
        } catch (Exception e) {
            e.printStackTrace();
        }


    }

    private static List<String> getCreateTableSqlMysql(String url,String dataBase,String user,String password,String tableName) throws Exception {

        // 建表语句list
        List<String> createTableList = new ArrayList<>();

        // 注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 获取连接
        String allUrl = url + "/" + dataBase + "?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(allUrl, user, password);

        // 获取执行sql的对象 Statement
        Statement stmt = conn.createStatement();
        // 表名list
        List<String> tableNameList = new ArrayList<>();
        if (tableName == null || "".equals(tableName)) {
            // 定义查询所有表名sql语句
            String queryTableNameSql = "SELECT table_name FROM information_schema.tables WHERE table_schema = '" + dataBase + "'";

            // 执行sql
            ResultSet tableNameResult = stmt.executeQuery(queryTableNameSql);

            // 展开结果集数据库
            while (tableNameResult.next()) {
                tableNameList.add(tableNameResult.getString("table_name"));
            }
        } else {
            tableNameList = Arrays.asList(tableName.replaceAll("，",",").split(","));
        }

        if (tableNameList.size() == 0) {
            return null;
        }

        //遍历表名
        for (String table : tableNameList) {

            //定义获取建表语句的sql
            String createTableSql = "SHOW CREATE TABLE " + table;

            //执行sql
            ResultSet createTableResult = stmt.executeQuery(createTableSql);
            while (createTableResult.next()) {
                try {
                    String createTable = createTableResult.getString("Create Table");
                    createTableList.add(createTable.replaceAll("CREATE TABLE","CREATE TABLE IF NOT EXISTS"));
                } catch (Exception e) {
                    continue;
                }

            }
        }

        //释放资源
        stmt.close();
        conn.close();

        return createTableList;
    }

    private static void executeCreateTableMysql(String url,String dataBase,String user,String password,List<String> createTableList) throws Exception {

        if (createTableList == null || createTableList.size() == 0) {
            return;
        }

        // 注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 获取连接
        String allUrl = url + "/" + dataBase + "?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(allUrl, user, password);
        // 获取执行sql的对象 Statement
        Statement stmt = conn.createStatement();

        // 执行
        exceteSql(stmt, createTableList);

        // 释放资源
        stmt.close();
        conn.close();
    }

    private static void exceteSql(Statement stmt, List<String> list) {
        List<String> newList = new ArrayList<>();
        for (String sql : list){
            try{
                //执行
                stmt.execute(sql);
                num++ ;
                System.out.println(num + "------自动建表成功-------");
            }catch(Exception e){
                newList.add(sql);
                continue;
            }
        }

        if (newList.size() > 0) {
            if (newList.size() < list.size()) {
                exceteSql(stmt, newList);
            } else {
                System.out.println("------自动建表失败-------");
            }

        }
    }
}
